import pandas as pd
import matplotlib.pyplot as plt
import os
from datetime import datetime
import matplotlib

# 设置中文字体
plt.rcParams['font.sans-serif'] = ['SimHei']  # 用来正常显示中文标签
plt.rcParams['axes.unicode_minus'] = False  # 用来正常显示负号

# 配置列名
COLUMN_NAMES = {
    'date': '创建时间',
    'product': '货品',
    'weight': '净重',
    'customer': '陆运付款账户',
    'location': '发货地',
    'plate': '车辆'
}

# 创建输出目录
os.makedirs('analysis_results', exist_ok=True)

# 自定义异常类
class DataAnalysisError(Exception):
    pass

def load_data(file_path):
    """加载Excel数据"""
    try:
        df = pd.read_excel(file_path)
        # 检查必要列是否存在
        required_columns = list(COLUMN_NAMES.values())
        missing_cols = [col for col in required_columns if col not in df.columns]
        if missing_cols:
            raise DataAnalysisError(f"Excel文件中缺少以下列: {', '.join(missing_cols)}")
        
        # 转换日期列为datetime类型
        df[COLUMN_NAMES['date']] = pd.to_datetime(df[COLUMN_NAMES['date']])
        return df
    except Exception as e:
        raise DataAnalysisError(f"数据加载失败: {e}")

def filter_june_data(df):
    """筛选6月份数据"""
    return df[(df[COLUMN_NAMES['date']].dt.month == 6)]

def analyze_mineral_daily(df):
    """分析每日矿粉货运量"""
    try:
        mineral_df = df[df[COLUMN_NAMES['product']].str.contains('矿粉', na=False)]
        daily_mineral = mineral_df.groupby(mineral_df[COLUMN_NAMES['date']].dt.day)[COLUMN_NAMES['weight']].sum()
        
        # 保存结果
        daily_mineral.to_csv('analysis_results/daily_mineral.csv', header=['矿粉货运量'])
        
        # 绘制柱状图
        plt.figure(figsize=(10, 6))
        daily_mineral.plot(kind='bar')
        plt.title('6月每日矿粉货运量趋势')
        plt.xlabel('日期')
        plt.ylabel('货运量(吨)')
        plt.xticks(rotation=0)
        plt.tight_layout()
        plt.savefig('analysis_results/daily_mineral.png')
        plt.close()
        
        return daily_mineral
    except Exception as e:
        raise DataAnalysisError(f"分析矿粉数据出错: {e}")

def analyze_cement_daily(df):
    """分析每日水泥货运量"""
    try:
        cement_df = df[df[COLUMN_NAMES['product']].str.contains('水泥', na=False)]
        daily_cement = cement_df.groupby(cement_df[COLUMN_NAMES['date']].dt.day)[COLUMN_NAMES['weight']].sum()
        
        # 保存结果
        daily_cement.to_csv('analysis_results/daily_cement.csv', header=['水泥货运量'])
        
        # 绘制柱状图
        plt.figure(figsize=(10, 6))
        daily_cement.plot(kind='bar')
        plt.title('6月每日水泥货运量趋势')
        plt.xlabel('日期')
        plt.ylabel('货运量(吨)')
        plt.xticks(rotation=0)
        plt.tight_layout()
        plt.savefig('analysis_results/daily_cement.png')
        plt.close()
        
        return daily_cement
    except Exception as e:
        raise DataAnalysisError(f"分析水泥数据出错: {e}")

def analyze_customer_demand(df):
    """分析客户需求"""
    try:
        customer_demand = df.groupby(COLUMN_NAMES['customer'])[COLUMN_NAMES['weight']].sum().sort_values(ascending=False)
        customer_demand.to_csv('analysis_results/customer_demand.csv', header=['货运需求量'])
        return customer_demand
    except Exception as e:
        raise DataAnalysisError(f"分析客户需求出错: {e}")

def analyze_location_total(df):
    """分析发货地总量"""
    try:
        location_total = df.groupby(COLUMN_NAMES['location'])[COLUMN_NAMES['weight']].sum().sort_values(ascending=False)
        location_total.to_csv('analysis_results/location_total.csv', header=['发货总量'])
        
        # 绘制饼图
        plt.figure(figsize=(10, 10))
        location_total.plot(kind='pie', autopct='%1.1f%%')
        plt.title('6月各发货地发货总量占比')
        plt.ylabel('')
        plt.tight_layout()
        plt.savefig('analysis_results/location_total.png')
        plt.close()
        
        return location_total
    except Exception as e:
        raise DataAnalysisError(f"分析发货地总量出错: {e}")

def analyze_plate_total(df):
    """分析车牌号总量"""
    try:
        plate_total = df.groupby(COLUMN_NAMES['plate'])[COLUMN_NAMES['weight']].sum().sort_values(ascending=False)
        plate_total.to_csv('analysis_results/plate_total.csv', header=['总货运量'])
        return plate_total
    except Exception as e:
        raise DataAnalysisError(f"分析车牌总量出错: {e}")

def generate_report():
    """生成分析报告"""
    try:
        report_content = """# A公司6月货运情况分析

## 1. 每日矿粉货运量趋势
![矿粉货运量趋势](analysis_results/daily_mineral.png)

## 2. 每日水泥货运量趋势
![水泥货运量趋势](analysis_results/daily_cement.png)

## 3. 客户货运需求量排名
```
"""
        
        # 添加客户需求数据
        with open('analysis_results/customer_demand.csv', 'r', encoding='utf-8') as f:
            report_content += f.read()
        
        report_content += """
```

## 4. 发货地发货总量占比
![发货地总量占比](analysis_results/location_total.png)

## 5. 车牌号货运量排名
```
"""
        
        # 添加车牌号数据
        with open('analysis_results/plate_total.csv', 'r', encoding='utf-8') as f:
            report_content += f.read()
        
        report_content += """
```

报告生成时间: """ + datetime.now().strftime('%Y-%m-%d %H:%M:%S')
        
        with open('A公司6月货运情况分析.md', 'w', encoding='utf-8') as f:
            f.write(report_content)
    except Exception as e:
        raise DataAnalysisError(f"生成报告出错: {e}")

def main():
    try:
        # 加载数据
        data_file = os.path.join(os.path.dirname(__file__), 'FhjlViewDD.xlsx')
        df = load_data(data_file)
        
        # 筛选6月数据
        june_df = filter_june_data(df)
        
        # 执行各项分析
        analyze_mineral_daily(june_df)
        analyze_cement_daily(june_df)
        analyze_customer_demand(june_df)
        analyze_location_total(june_df)
        analyze_plate_total(june_df)
        
        # 生成报告
        generate_report()
        
        print("分析完成，结果已保存在analysis_results目录和'A公司6月货运情况分析.md'文件中")
    except DataAnalysisError as e:
        print(f"分析过程中出错: {e}")

if __name__ == '__main__':
    main()